﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlUserPrivilegeRepository 
    {
        public PagedList<Data.UserPrivilege> GetUserPrivilegeList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [UserPrivilege]) a where a.rownum >= @paramStart and a.rownum <= @paramEnd";
                        cmd.Parameters.Add(new SqlParameter("paramStart", start));
                        cmd.Parameters.Add(new SqlParameter("paramEnd", start + limit));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.UserPrivilege> userPrivilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            userPrivilegeList = new List<Data.UserPrivilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.UserPrivilege userPrivilege = new Data.UserPrivilege();
                                userPrivilege.Id = new Guid(dr["Id"].ToString());
                                userPrivilege.UserId = new Guid(dr["UserId"].ToString());
                                userPrivilege.PrivilegeId = new Guid(dr["PrivilegeId"].ToString());
                                userPrivilegeList.Add(userPrivilege);
                            }
                            return new PagedList<Data.UserPrivilege>(userPrivilegeList, start, limit, userPrivilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CreateUserPrivilege(DbContext context, Data.UserPrivilege userPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into [UserPrivilege](Id, UserId, PrivilegeId) values(@paramId, @paramUserId, @paramPrivilegeId)";
                    cmd.Parameters.Add(new SqlParameter("paramId", userPrivilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramUserId", userPrivilege.UserId));
                    cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", userPrivilege.PrivilegeId));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveUserPrivilege(DbContext context, Data.UserPrivilege userPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [UserPrivilege] where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", userPrivilege.Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateUserPrivilege(DbContext context, Data.UserPrivilege userPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update [UserPrivilege] set UserId = @paramUserId, PrivilegeId = @paramPrivilegeId where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", userPrivilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramUserId", userPrivilege.UserId));
                    cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", userPrivilege.PrivilegeId));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public PagedList<Data.UserPrivilege> GetUserPrivilege(DbContext context, Guid userId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [UserPrivilege] where userId = @paramUserId";
                        cmd.Parameters.Add(new SqlParameter("paramUserId", userId));
                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.UserPrivilege> userPrivilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            userPrivilegeList = new List<Data.UserPrivilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.UserPrivilege userPrivilege = new Data.UserPrivilege();
                                userPrivilege.Id = new Guid(dr["Id"].ToString());
                                userPrivilege.UserId = new Guid(dr["UserId"].ToString());
                                userPrivilege.PrivilegeId = new Guid(dr["PrivilegeId"].ToString());
                                userPrivilegeList.Add(userPrivilege);
                            }
                            return new PagedList<Data.UserPrivilege>(userPrivilegeList, 0, int.MaxValue, userPrivilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public bool RemoveUserPrivilege(DbContext context, Data.User user)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [UserPrivilege] where UserId = @paramUserId";
                    cmd.Parameters.Add(new SqlParameter("paramUserId", user.Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveUserPrivilege(DbContext context, Data.Privilege privilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [UserPrivilege] where PrivilegeId = @paramPrivilegeId";
                    cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", privilege.Id));
                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
